package com.hezhou.jenkins.my_jenkins_test.controller;


import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelWriter;
import com.hezhou.jenkins.my_jenkins_test.entity.SysUser;
import com.hezhou.jenkins.my_jenkins_test.json.CodeMsg;
import com.hezhou.jenkins.my_jenkins_test.json.HeZhouResult;
import com.hezhou.jenkins.my_jenkins_test.service.SysUserService;
import com.hezhou.jenkins.util.SysUserPOIUtil;
import com.hezhou.jenkins.util.SysUserReadExcel;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.support.incrementer.HsqlMaxValueIncrementer;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * <p>
 * 用户表 前端控制器
 * </p>
 *
 * @author hezhou
 * @since 2020-04-23
 */
@RestController(value = "CmlBdRegisterController")
@Api(tags = "用户模块api")
@RequestMapping("/sys-user")
public class SysUserController {

    private Logger logger = LoggerFactory.getLogger(SysUserController.class);

    @Autowired
    private SysUserService sysUserService;

    /**
     * 创建HSSFWorkbook对象(excel的文档对象)
     */
    private  HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

    @ApiOperation("导出用户表excel")
    @GetMapping("/excel/out")
    public HeZhouResult<?> getPageByCondition(){

        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
        //设置水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置文本颜色
        HSSFFont hssfFont = hssfWorkbook.createFont();
        hssfFont.setFontName("微软雅黑");
        hssfFont.setFontHeight((short)(10*25));
        hssfFont.setColor(Font.COLOR_RED);
        cellStyle.setFont(hssfFont);

        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = hssfWorkbook.createSheet("洲大大平台用户列表");

        //设置单元格的行高和列宽
        //sheet.setDefaultRowHeightInPoints(10); //设置缺省列高//设置缺省列宽
        sheet.setDefaultColumnWidth(20);
        ////在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row = sheet.createRow(0);
        //设置行高
        row.setHeight((short)(2*256));
        
        //创建HSSFCell对象
        HSSFCell cell = row.createCell(0);
        //设置单元格的值(设置单元格内容)
        cell.setCellValue("用户信息一览表");
        cell.setCellStyle(cellStyle);

        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列，截至列
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,7));

        //写入表头的信息 --
        HSSFRow row1=sheet.createRow(1);
        row1.createCell(0).setCellValue("id");
        row1.createCell(1).setCellValue("名字");
        row1.createCell(2).setCellValue("昵称");
        row1.createCell(3).setCellValue("电话");
        row1.createCell(4).setCellValue("电子邮箱");
        row1.createCell(5).setCellValue("是否可用");
        row1.createCell(6).setCellValue("创建时间");
        row1.createCell(7).setCellValue("修改时间");
        //拿到数据
        List<SysUser> list = sysUserService.list();
        for (int i = 0; i < list.size(); i++) {
            HSSFRow info = sheet.createRow((int) i + 2);
            SysUser user = list.get(i);
            info.createCell(0).setCellValue(user.getId());
            info.createCell(1).setCellValue(user.getUsername());
            info.createCell(2).setCellValue(user.getNickName());
            info.createCell(3).setCellValue(user.getMobile());
            info.createCell(4).setCellValue(user.getEmail());
            info.createCell(5).setCellValue(user.getIsEnabled());
            info.createCell(6).setCellValue(user.getCreateDate().toString());
            info.createCell(7).setCellValue(user.getUpdateDate().toString());
        }
        Long time = System.currentTimeMillis();
        String fileName = "d:\\workbook"+time+".xls";
        //输出Excel文件
        try(FileOutputStream output =new FileOutputStream(fileName)){
            hssfWorkbook.write(output);
        }catch (Exception e){
            return HeZhouResult.error(CodeMsg.SERVER_EXCEPTION,"文件导出失败!");
        }
        return  HeZhouResult.success(true);
    }

    @ApiOperation("新版导出用户表excel")
    @GetMapping("/newExcel/out")
    public HeZhouResult<?> NewExcelOut(){
        List<SysUser> dataVOList = sysUserService.list();
        // 写入数据到工作簿对象内
        Workbook workbook = SysUserPOIUtil.exportData(dataVOList);
        // 以文件的形式输出工作簿对象
        FileOutputStream fileOut = null;
        try {
            Long time = System.currentTimeMillis();
            String fileName = "d:\\workbook"+time+".xlsx";
            File exportFile = new File(fileName);
            if (!exportFile.exists()) {
                exportFile.createNewFile();
            }
            fileOut = new FileOutputStream(fileName);
            workbook.write(fileOut);
            fileOut.flush();
        } catch (Exception e) {
            logger.warn("输出Excel时发生错误，错误原因：" + e.getMessage());
        } finally {
            try {
                if (null != fileOut) {
                    fileOut.close();
                }
                if (null != workbook) {
                    workbook.close();
                }
            } catch (IOException e) {
                logger.warn("关闭输出流时发生错误，错误原因：" + e.getMessage());
            }
        }
        return HeZhouResult.success(true);
    }

    @ApiOperation("解析用户表excel")
    @GetMapping("/newExcel/read")
    public HeZhouResult<?> NewExcelRead(){
        // 设定Excel文件所在路径
        String excelFileName = "D:\\workbook1588910342165.xlsx";
        List<SysUser> readResult = SysUserReadExcel.readExcel(excelFileName);
        return HeZhouResult.success(readResult);
    }

    @ApiOperation("查询出当前的用户")
    @GetMapping("/user/list")
    public HeZhouResult<?> userList(){
        List<SysUser> list = sysUserService.list();
        return HeZhouResult.success(list);
    }



    /**
     * 设置head样式
     */
    private HSSFCellStyle headFont() {
        HSSFFont headFont = hssfWorkbook.createFont();
        headFont.setFontName("微软雅黑");
        headFont.setFontHeightInPoints((short) 11);
        HSSFCellStyle headStyle = hssfWorkbook.createCellStyle();
        headStyle.setFont(headFont);
        headStyle.setBorderTop(BorderStyle.HAIR);
        headStyle.setBorderRight(BorderStyle.DASH_DOT);
        headStyle.setBorderBottom(BorderStyle.DASH_DOT);
        headStyle.setBorderLeft(BorderStyle.DASH_DOT);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        return headStyle;
    }



}
